/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.TreeSet;

/**
 *
 * @author sio
 */
public class PaysDAO {

    static Collection<Dep> getLesDeps() {

        try {

            Connection con = Connect.get();
            Statement reqD;
            reqD = con.createStatement();
            ResultSet rsD = reqD.executeQuery("select distinct departement from medecin");
            Collection<Dep> d = new TreeSet<Dep>();

            //Parcours
            Collection<Med> m;

            while (rsD.next()) {
                m = new TreeSet<Med>();
                Statement reqM = con.createStatement();
                ResultSet rsM = reqM.executeQuery("select * from medecin"
                        + " where departement = " + rsD.getString("departement"));

                while (rsM.next()) {
                    m.add(new Med(rsM.getString("nom"), rsM.getString("prenom"), rsM.getString("adresse"),
                            rsM.getString("tel"), rsM.getString("specialitecomplementaire"), rsM.getString("id")));

                }
                rsM.close();
                Dep unDep = new Dep(rsD.getString("departement"), m);
                d.add(unDep);
            }
            return d;
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
            return null;
        }
    }

    static Collection<Spe> getLesSpes() {
        try {
            Collection<Spe> colSpe = new TreeSet<Spe>();
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct specialitecomplementaire from medecin");

            while (rs.next()) {

                if (rs.getString("specialitecomplementaire") != null) {

                    Collection<Med> med;
                    med = new TreeSet<Med>();
                    Statement reqM = con.createStatement();
                    ResultSet rsM = reqM.executeQuery("select * from medecin"
                            + " where specialitecomplementaire = '" + rs.getString("specialitecomplementaire") + "'");

                    while (rsM.next()) {

                        med.add(new Med(rsM.getString("nom"), rsM.getString("prenom"), rsM.getString("adresse"),
                                rsM.getString("tel"), rsM.getString("specialitecomplementaire"), rsM.getString("id")));
                    }
                    Spe spe = new Spe(rs.getString("specialitecomplementaire"), med);
                    colSpe.add(spe);
                }
            }
            return colSpe;
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
            return null;
        }
    }

}
